﻿using Abp.EntityFrameworkCore;
using HiShing.TongDanGuanJia.DeviceDataHistories;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;

namespace HiShing.TongDanGuanJia.EntityFrameworkCore.Repositories
{
    public class DeviceDataHistoryRepository : TongDanGuanJiaRepositoryBase<DeviceDataHistory, string>, IDeviceDataHistoryRepository
    {
        public DeviceDataHistoryRepository(IDbContextProvider<TongDanGuanJiaDbContext> dbContextProvider) : base(dbContextProvider)
        {
        }

        public List<DeviceMonthDataHistory> GetDevicePerMonthDataHistories(string deviceId, DateTime startData, DateTime endDate)
        {
            var sql = "select NEWID() as Id, @p0 as DeviceId,";
            var split = string.Empty;
            for (var i = 1; i <= 101; i++)
            {
                sql += $" {split} sum(Column{i})";
                split = "+";
            }
            sql += " as TotalAccount, FORMAT(AcquisitionTime,'yyyy-MM') AS Month ";
            sql += "from DeviceDataHistory where  deviceID=@p0  and AcquisitionTime>=@p1 and AcquisitionTime < @p2  " +
                "group by FORMAT(AcquisitionTime,'yyyy-MM')";

            var context = GetContext();
            var entities = context.DeviceMonthDataHistories.FromSqlRaw(sql, deviceId, startData, endDate).ToList();
            return entities;
        }
    }
}
